package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.config.AppConfigProperties
import com.opennews.openplatform.familyexpenseapi.entity.Tables.APP_RELEASE_LOG
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import com.opennews.openplatform.familyexpenseapi.jooq.RecordHelper
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.springframework.core.env.Environment
import org.springframework.stereotype.Service
import java.time.LocalDateTime

@Service
class AppReleaseLogService(
    private val appConfigProperties: AppConfigProperties,
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val env: Environment,
) {
    /**
     * Prepares the initial data for AppReleaseLog.
     */
    fun init() {
        if (!dslContextWrapper.exists(APP_RELEASE_LOG)) {
            dslContext.newRecord(APP_RELEASE_LOG).run {
                RecordHelper.setCommonFields(this)
                releaseDate = LocalDateTime.of(2019, 12, 1, 0, 0)
                appVersion = "1.0.0"
                changeLog = "初始版本"
                apkFilename = "family-expense.apk"
                appStoreUrl = "https://"
                insert()
            }
        }
    }

    /**
     * Queries the app release details by its version.
     *
     * @param appVersion The string of the app version.
     * @return A map containing:
     *  - releaseDate: Date when the app was released
     *  - appVersion: String of the app version
     *  - apkFilename: String of the APK filename
     *  - apkUrl: String of the APK URL for Android
     *  - isMandatory: Whether this is a mandatory update
     */
    fun queryByAppVersion(appVersion: String): Map<String, Any?>? {
        val result = dslContext
            .select(
                APP_RELEASE_LOG.RELEASE_DATE.`as`("releaseDate"),
                APP_RELEASE_LOG.APP_VERSION.`as`("appVersion"),
                APP_RELEASE_LOG.APK_FILENAME.`as`("apkFilename"),
                APP_RELEASE_LOG.IS_MANDATORY.`as`("isMandatory"),
            )
            .from(APP_RELEASE_LOG)
            .where(APP_RELEASE_LOG.APP_VERSION.eq(appVersion))
            .fetchOneMap()

        return result?.toMutableMap()?.apply {
            val baseUrl = appConfigProperties.resource.httpBaseUrl
            val apkDir = appConfigProperties.resource.avatarDirectoryName
            val apkFilename = this["apkFilename"]
            this["apkUrl"] = "$baseUrl/$apkDir/$apkFilename"
        }
    }

    /**
     * Queries the latest version which was released after the provided app version.
     *
     * @param appVersion The string of the app version.
     * @return A map containing:
     *  - releaseDate: Date when the app was released
     *  - appVersion: String of the app version
     *  - changeLog: String of the release change log
     *  - apkFilename: String of the APK filename
     *  - apkUrl: String of the APK URL for Android
     *  - appStoreUrl: String of the App Store URL for iOS
     *  - isMandatory: Whether this is a mandatory update
     */
    fun queryLatestVersion(appVersion: String): Map<String, Any?>? {
        // Different database environment causes different date type value.
        // For example, the value in database is '2019-12-31 00:00:00' but the retrieved value
        // might be '2019-12-31 14:00:00'. It might be caused by time zone setting.
        // That's why using sub-query in where instead of using the input releaseDate as parameter.
        val releaseDateQuery = dslContext.select(APP_RELEASE_LOG.RELEASE_DATE)
            .from(APP_RELEASE_LOG)
            .where(APP_RELEASE_LOG.APP_VERSION.eq(appVersion))

        val isMandatoryQuery = dslContext.select(DSL.count(APP_RELEASE_LOG.ID))
            .from(APP_RELEASE_LOG)
            .where(
                APP_RELEASE_LOG.RELEASE_DATE.gt(releaseDateQuery)
                    .and(APP_RELEASE_LOG.IS_MANDATORY.isTrue())
            )
            .asField<Int>()

        val result = dslContext
            .select(
                APP_RELEASE_LOG.RELEASE_DATE.`as`("releaseDate"),
                APP_RELEASE_LOG.APP_VERSION.`as`("appVersion"),
                APP_RELEASE_LOG.CHANGE_LOG.`as`("changeLog"),
                APP_RELEASE_LOG.APK_FILENAME.`as`("apkFilename"),
                APP_RELEASE_LOG.APP_STORE_URL.`as`("appStoreUrl"),
                isMandatoryQuery.gt(0).`as`("isMandatory"),
            )
            .from(APP_RELEASE_LOG)
            .where(APP_RELEASE_LOG.RELEASE_DATE.gt(releaseDateQuery))
            .orderBy(APP_RELEASE_LOG.RELEASE_DATE.desc())
            .limit(1)
            .fetchOneMap()

        return result?.toMutableMap()?.apply {
            val baseUrl = appConfigProperties.resource.httpBaseUrl
            val apkDir = appConfigProperties.resource.apkDirectoryName
            val apkFilename = this["apkFilename"]
            this["apkUrl"] = "$baseUrl/$apkDir/$apkFilename"
        }
    }

    /**
     * Lists change log info.
     *
     * @param pageSize How many records to return in one trip.
     * @param pageIndex Where to start taking rows.
     * @return A list of maps, each containing:
     *  - id: String of the app release log id
     *  - releaseDate: Date of this version released
     *  - appVersion: The string of this app version
     *  - changeLog: The string of this change log info
     *  - isMandatory: Whether this is a mandatory update
     */
    fun listChangeLog(pageSize: Int, pageIndex: Int): List<Map<String, Any?>> {
        return dslContext
            .select(
                APP_RELEASE_LOG.ID.`as`("id"),
                APP_RELEASE_LOG.RELEASE_DATE.`as`("releaseDate"),
                APP_RELEASE_LOG.APP_VERSION.`as`("appVersion"),
                APP_RELEASE_LOG.CHANGE_LOG.`as`("changeLog"),
                APP_RELEASE_LOG.IS_MANDATORY.`as`("isMandatory"),
            )
            .from(APP_RELEASE_LOG)
            .orderBy(APP_RELEASE_LOG.RELEASE_DATE.desc())
            .limit(pageSize)
            .offset(pageIndex * pageSize)
            .fetchMaps()
    }
}